﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data.SqlTypes;

/// <summary>
/// Summary description for FriendDatabaseAdapter
/// </summary>
public class FriendDatabaseAdapter
{
    public const string REGISTRATION_TABLE = "Registration";
    public const string USER_NAME = "Username";
    public const string FIRST_NAME = "Firstname";
    public const string LAST_NAME = "Lastname";
    public const string EMAIL_ID = "EmailID";

    private string connectionString = "Server=.\\SQLEXPRESS;Initial Catalog=Social Klee;Integrated security=SSPI;";
    private SqlConnection connection;
    private List<Friend> allFriends;

	public FriendDatabaseAdapter()
	{
        this.allFriends = new List<Friend>();
	}

    public string ConnectionString { get; set; }
    public List<Friend> AllFriends { get; set; }

    public void Open()
    {
        connection = new SqlConnection(connectionString);
        connection.Open();
    }

    public void Close()
    {
        connection.Close();
    }

    /// <summary>
    /// Checks if database exists.
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns>
    /// returns true if table exists in database
    /// </returns>
    /// 
    public bool DoesTableExist(string tableName)
    {
        bool doesExist = false;
        string commandString = string.Format("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", tableName);
        //string commandString = string.Format("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", "tableNothing");
        SqlCommand command = new SqlCommand(commandString, connection);

        try
        {
            Object result = command.ExecuteScalar();

            if (result != null)
                doesExist = true;
        }
        catch (Exception ex) { }

        return doesExist;
    }

    public List<Friend> GetAllFriends()
    {
        string commandString = string.Format("Select * from {0}", REGISTRATION_TABLE);

        using (SqlCommand command = new SqlCommand(commandString, this.connection))
        {
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                //Int64 userID = reader.GetInt64(0);
                string userName = reader.GetString(1);
                string firstName = reader.GetString(3);
                string lastName = reader.GetString(4);
                string emailID = reader.GetString(10);
                allFriends.Add(new Friend(firstName, lastName, userName, emailID));
            }
        }

        return this.allFriends;
    }

}